
/*********************
 **  CreateTime: 2006-7-26 
 **********************/

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using EntLibEC.Cmn;
using EntLibEC.Info.PIM;


namespace EntLibEC.Dac.PIM
{
    /// <summary>
    /// Contains the the data access methods for ProductPropertyDA.
    /// </summary>
    public abstract class ProductPropertyDA
    {
        #region Sql Script

        private const string SQL_GET_PRODUCTPROPERTY_BY_PRODUCT
             = @"SELECT a.ProductSysNo,a.GroupSysNo,a.PropertySysNo,a.ValueSysNo,a.ManualInput,a.IsActive,a.LastEditUserSysNo,a.LastEditTime,b.UserName as LastEditUsername,c.GroupDescription AS GroupName
				FROM PIM_ProductProperty a (NOLOCK)
                LEFT JOIN Ent_Sys_User b (NOLOCK) ON a.LastEditUserSysNo = b.SysNo
                LEFT JOIN PIM_PropertyGroup c (NOLOCK) ON a.GroupSysNo = c.SysNo
				WHERE ProductSysNo = @ProductSysNo";

        private const string SQL_INSERT_PRODUCTPROPERTY = "INSERT INTO PIM_ProductProperty(ProductSysNo,PropertySysNo,ValueSysNo,ManualInput,IsActive,LastEditUserSysNo,LastEditTime) VALUES (@ProductSysNo,@PropertySysNo,@ValueSysNo,@ManualInput,@IsActive,@LastEditUserSysNo,GETDATE())";

        private const string SQL_DELETE_PRODUCTPROPERTY_BY_PRODUCT = "DELETE FROM PIM_ProductProperty WHERE ProductSysNo = @ProductSysNo";
        #endregion

        protected static ProductPropertyInfo Map(DataRow row)
        {
            ProductPropertyInfo info = new ProductPropertyInfo();

            info.ProductSysNo = Convert.ToInt32(row["ProductSysNo"]);
            info.GroupSysNo = Convert.ToInt32(row["GroupSysNo"]);
            info.PropertySysNo = Convert.ToInt32(row["PropertySysNo"]);
            info.ValueSysNo = Convert.ToInt32(row["ValueSysNo"]);
            info.ManualInput = row["ManualInput"].ToString();
            info.IsActive = Convert.ToInt32(row["IsActive"]) == 1;
            info.LastEditUserSysNo = Convert.ToInt32(row["LastEditUserSysNo"]);
            info.LastEditTime = Convert.ToDateTime(row["LastEditTime"]);

            info.LastEditUsername = row["LastEditUsername"].ToString();
            info.GroupName = row["GroupName"].ToString();

            return info;
        }

        public static List<ProductPropertyInfo> GetProductProperties(int ProductSysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@ProductSysNo", SqlDbType.Int);
            parms[0].Value = ProductSysNo;

            DataTable dt = DataAccessFacade.GetDataTable(SQL_GET_PRODUCTPROPERTY_BY_PRODUCT, parms);
            if (dt == null || dt.Rows.Count == 0)
                return null;

            List<ProductPropertyInfo> list = new List<ProductPropertyInfo>();
            foreach (DataRow row in dt.Rows)
                list.Add(Map(row));

            return list;
        }

        public static void InsertProductProperty(ProductPropertyInfo info)
        {
            SqlParameter[] parms = new SqlParameter[]{
                new SqlParameter("@ProductSysNo", SqlDbType.Int),
                new SqlParameter("@PropertySysNo", SqlDbType.Int),
                new SqlParameter("@ValueSysNo", SqlDbType.Int),
                new SqlParameter("@ManualInput", SqlDbType.NVarChar, 2000),
                new SqlParameter("@IsActive", SqlDbType.Int),
                new SqlParameter("@LastEditUserSysNo", SqlDbType.Int) };

            parms[0].Value = info.ProductSysNo;
            parms[1].Value = info.PropertySysNo;
            parms[2].Value = info.ValueSysNo;
            parms[3].Value = info.ManualInput;
            parms[4].Value = info.IsActive ? 1 : 0;
            parms[5].Value = info.LastEditUserSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_INSERT_PRODUCTPROPERTY, parms);
        }

        public static void DeleteProductProperty(int ProductSysNo)
        {
            SqlParameter[] parms = new SqlParameter[1];

            parms[0] = new SqlParameter("@ProductSysNo", SqlDbType.Int);
            parms[0].Value = ProductSysNo;

            DataAccessFacade.ExecSqlCommand(SQL_DELETE_PRODUCTPROPERTY_BY_PRODUCT, parms);
        }
    }

}
